package com.edu.hre.db.sql.recruit;

import java.io.Serializable;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import com.edu.hre.common.utils.CommonUtils;
import com.edu.hre.db.pojo.HreJobCandidate;
import com.edu.hre.db.pojo.HreJobVacancy;
import com.edu.hre.db.service.BaseService;
import com.edu.hre.db.utils.Compare;
import com.edu.hre.struts.action.recruit.VacancyUpdateAction;
import com.edu.hre.struts.form.recruit.CandidateForm;
import com.edu.hre.struts.form.recruit.CandidateListForm;
import com.edu.hre.struts.form.recruit.VacancyUpdateForm;

import edu.emory.mathcs.backport.java.util.Collections;

public class CandidateDAOService implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 3394301416150421253L;
	public static final String BEAN_NAME = "candidateDAOService";
	private BaseService baseService  = null;

	public void setBaseService(BaseService baseService) {
		this.baseService = baseService;
	} 

	public List<CandidateForm> getListCandidateForm(CandidateListForm form, int limit, int offset){
		
		List<CandidateForm> listResult = new ArrayList<CandidateForm>();
		String sql = "";
		ArrayList<Object> params = new ArrayList<Object>();
		
		boolean isEdu = form.getSelectedEducation() != null && !"".equals(form.getSelectedEducation())
				&& !"0".equals(form.getSelectedEducation());
		
		boolean isLang = form.getSelectedLanguage() != null && !"".equals(form.getSelectedLanguage())
				&& !"0".equals(form.getSelectedLanguage());

		boolean isLic = form.getSelectedLicense() != null && !"".equals(form.getSelectedLicense())
				&& !"0".equals(form.getSelectedLicense());
		
		boolean isSki = form.getSelectedSkill() != null && !"".equals(form.getSelectedSkill())
				&& !"0".equals(form.getSelectedSkill());
		
		boolean isName = form.getNameKeyWord() != null && !"".equals(form.getNameKeyWord())
				&& !"0".equals(form.getNameKeyWord());

		sql = "SELECT can.id AS col1," +
					" CONCAT(can.last_name, ' ', can.middle_name, ' ', can.first_name)  AS col2, " +
					" can.email AS col3, " +
					" can.contact_number AS col4 " +
					
					" FROM hre_job_candidate AS can ";
					
		if (isEdu) {
			sql +=	" , hre_candidate_education AS edu ";
		}
		if (isLang) {
			sql +=	" , hre_candidate_language AS lan ";
		}
		if (isLic) {
			sql +=	" , hre_candidate_license AS lic ";
		}
		if (isSki) {
			sql +=	" , hre_candidate_skill AS ski ";
		}
		
		sql +=	" WHERE can.is_deleted = 0 ";
		
		if (isEdu) {
			sql +=	" AND can.id = edu.candidate_id ";
			sql +=	" AND edu.education_id = ? ";
			params.add(form.getSelectedEducation());
		}
		if (isLang) {
			sql +=	" AND can.id = lan.candidate_id ";
			sql +=	" AND lan.language_id = ? ";
			params.add(form.getSelectedLanguage());
		}
		if (isLic) {
			sql +=	" AND can.id = lic.candidate_id ";
			sql +=	" AND lic.license = ? ";
			params.add(form.getSelectedLicense());
		}
		if (isSki) {
			sql +=	" AND can.id = ski.candidate_id ";
			sql +=	" AND ski.skill = ? ";
			params.add(form.getSelectedSkill());
		}
		
		if (isName) {
			sql +=	" AND (CONCAT(can.last_name, ' ', can.middle_name, ' ', can.first_name) LIKE '%" + form.getNameKeyWord() + "%' " +
					" OR CONCAT(can.last_name, ' ', can.first_name) LIKE '%" + form.getNameKeyWord() + "%' )";
		}

		sql += " ORDER BY can.date_created DESC";

		List <Object[]> listObject = baseService.listArrayObject(sql, limit, offset, params.toArray());
		for(int i = 0; listObject != null && i < listObject.size(); i++){
			
			try {

				Object[] objs = listObject.get(i);
				CandidateForm candidateInfo = new CandidateForm();

				candidateInfo.setId(CommonUtils.normalizeObjectToString(objs[1], "0"));
				candidateInfo.setName(CommonUtils.normalizeObjectToString(objs[2], ""));
				candidateInfo.setEmail(CommonUtils.normalizeObjectToString(objs[3], ""));
				candidateInfo.setContactNumber(CommonUtils.normalizeObjectToString(objs[4], ""));

				listResult.add(candidateInfo);
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}

		return listResult;
	}
	
	public int countCandidateForm(CandidateListForm form){

		String sql = "";
		ArrayList<Object> params = new ArrayList<Object>();
		
		boolean isEdu = form.getSelectedEducation() != null && !"".equals(form.getSelectedEducation())
				&& !"0".equals(form.getSelectedEducation());
		
		boolean isLang = form.getSelectedLanguage() != null && !"".equals(form.getSelectedLanguage())
				&& !"0".equals(form.getSelectedLanguage());

		boolean isLic = form.getSelectedLicense() != null && !"".equals(form.getSelectedLicense())
				&& !"0".equals(form.getSelectedLicense());
		
		boolean isSki = form.getSelectedSkill() != null && !"".equals(form.getSelectedSkill())
				&& !"0".equals(form.getSelectedSkill());
		
		boolean isName = form.getNameKeyWord() != null && !"".equals(form.getNameKeyWord())
				&& !"0".equals(form.getNameKeyWord());

		sql = "SELECT COUNT(*) AS col1" +
					
					" FROM hre_job_candidate AS can ";
					
		if (isEdu) {
			sql +=	" , hre_candidate_education AS edu ";
		}
		if (isLang) {
			sql +=	" , hre_candidate_language AS lan ";
		}
		if (isLic) {
			sql +=	" , hre_candidate_license AS lic ";
		}
		if (isSki) {
			sql +=	" , hre_candidate_skill AS ski ";
		}
		
		sql +=	" WHERE can.is_deleted = 0 ";
		
		if (isEdu) {
			sql +=	" AND can.id = edu.candidate_id ";
			sql +=	" AND edu.education_id = ? ";
			params.add(form.getSelectedEducation());
		}
		if (isLang) {
			sql +=	" AND can.id = lan.candidate_id ";
			sql +=	" AND lan.language_id = ? ";
			params.add(form.getSelectedLanguage());
		}
		if (isLic) {
			sql +=	" AND can.id = lic.candidate_id ";
			sql +=	" AND lic.license = ? ";
			params.add(form.getSelectedLicense());
		}
		if (isSki) {
			sql +=	" AND can.id = ski.candidate_id ";
			sql +=	" AND ski.skill = ? ";
			params.add(form.getSelectedSkill());
		}
		
		if (isName) {
			sql +=	" AND (CONCAT(can.last_name, ' ', can.middle_name, ' ', can.first_name) LIKE '%" + form.getNameKeyWord() + "%' " +
					" OR CONCAT(can.last_name, ' ', can.first_name) LIKE '%" + form.getNameKeyWord() + "%' )";
		}

		try {
			List <Object[]> listObject = baseService.listArrayObject(sql, 0, 0, params.toArray());
			Object o [] = listObject.get(0);
	
			return ((BigInteger) o[1]).intValue();
		} catch (Exception e) {
			CommonUtils.println(e.getMessage());
			return 0;
		}
	}
	
	public void deleteCandidates(CandidateListForm form){

		ArrayList<CandidateForm> info = (ArrayList<CandidateForm>) form.getAllCandidates();
		for (int i = 0; i < info.size(); i++) {
			
			try {

				CandidateForm can = info.get(i);
				if (can.isSelected()) { 
	
					HreJobCandidate tmp = baseService.getFirstPojo("id", Compare.EQUAL, Integer.parseInt(can.getId()), null, null, 0, HreJobCandidate.class);
					tmp.setIsDeleted(true);
					baseService.saveOrUpdate(tmp);
				}
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}
	}
	
	public List<CandidateForm> getRecommendedCandidates (int vacId, int limit) {
		
		List<Integer> validIds = new ArrayList<Integer>();
		
		List<CandidateForm> listResult1Edu = null;
		List<CandidateForm> listResult1Major = null;
		List<CandidateForm> listResult2Edu = null;
		List<CandidateForm> listResult2Major = null;
		List<CandidateForm> listResult3Edu = null;
		List<CandidateForm> listResult3Major = null;
		List<CandidateForm> listResultLang1 = null;
		List<CandidateForm> listResultLang2 = null;
		List<CandidateForm> listResultLang3 = null;
		List<CandidateForm> listResultSkill1 = null;
		List<CandidateForm> listResultSkill2 = null;
		List<CandidateForm> listResultSkill3 = null;
		List<CandidateForm> listResultSkill4 = null;
		List<CandidateForm> listResultSkill5 = null;
		List<CandidateForm> listResultLicense1 = null;
		List<CandidateForm> listResultLicense2 = null;
		List<CandidateForm> listResultLicense3 = null;
		List<CandidateForm> listResultLicense4 = null;
		List<CandidateForm> listResultLicense5 = null;
		
		VacancyUpdateForm beanForm = new VacancyUpdateForm();
		getParams(vacId, beanForm);
		
		listResult1Edu = baseService.listRecommendedCandidates("candidate_filter_edu", new Integer[]{beanForm.getEdu1Level(), beanForm.getEdu1LevelImp()});
		listResult1Major = baseService.listRecommendedCandidates("candidate_filter_major", new Integer[]{beanForm.getEdu1Major(), beanForm.getEdu1MajorImp()});
		listResult2Edu = baseService.listRecommendedCandidates("candidate_filter_edu", new Integer[]{beanForm.getEdu2Level(), beanForm.getEdu2LevelImp()});
		listResult2Major = baseService.listRecommendedCandidates("candidate_filter_major", new Integer[]{beanForm.getEdu2Major(), beanForm.getEdu2MajorImp()});
		listResult3Edu = baseService.listRecommendedCandidates("candidate_filter_edu", new Integer[]{beanForm.getEdu3Level(), beanForm.getEdu3LevelImp()});
		listResult3Major = baseService.listRecommendedCandidates("candidate_filter_major", new Integer[]{beanForm.getEdu3Major(), beanForm.getEdu3MajorImp()});
		
		listResultLang1 = baseService.listRecommendedCandidates("candidate_filter_lang", new Integer[]{beanForm.getLang1(), beanForm.getLang1Imp(), beanForm.getLang1ReadImp(), beanForm.getLang1SpeakImp(), beanForm.getLang1ListenImp(), beanForm.getLang1WriteImp()});
		listResultLang2 = baseService.listRecommendedCandidates("candidate_filter_lang", new Integer[]{beanForm.getLang2(), beanForm.getLang2Imp(), beanForm.getLang2ReadImp(), beanForm.getLang2SpeakImp(), beanForm.getLang2ListenImp(), beanForm.getLang2WriteImp()});
		listResultLang3 = baseService.listRecommendedCandidates("candidate_filter_lang", new Integer[]{beanForm.getLang3(), beanForm.getLang3Imp(), beanForm.getLang3ReadImp(), beanForm.getLang3SpeakImp(), beanForm.getLang3ListenImp(), beanForm.getLang3WriteImp()});
		listResultSkill1 = baseService.listRecommendedCandidates("candidate_filter_skill", new Integer[]{beanForm.getSkill1(), beanForm.getSkill1Imp()});
		listResultSkill2 = baseService.listRecommendedCandidates("candidate_filter_skill", new Integer[]{beanForm.getSkill2(), beanForm.getSkill2Imp()});
		listResultSkill3 = baseService.listRecommendedCandidates("candidate_filter_skill", new Integer[]{beanForm.getSkill3(), beanForm.getSkill3Imp()});
		listResultSkill4 = baseService.listRecommendedCandidates("candidate_filter_skill", new Integer[]{beanForm.getSkill4(), beanForm.getSkill4Imp()});
		listResultSkill5 = baseService.listRecommendedCandidates("candidate_filter_skill", new Integer[]{beanForm.getSkill5(), beanForm.getSkill5Imp()});
		listResultLicense1 = baseService.listRecommendedCandidates("candidate_filter_license", new Integer[]{beanForm.getLicense1(), beanForm.getLicense1Imp()});
		listResultLicense2 = baseService.listRecommendedCandidates("candidate_filter_license", new Integer[]{beanForm.getLicense2(), beanForm.getLicense2Imp()});
		listResultLicense3 = baseService.listRecommendedCandidates("candidate_filter_license", new Integer[]{beanForm.getLicense3(), beanForm.getLicense3Imp()});
		listResultLicense4 = baseService.listRecommendedCandidates("candidate_filter_license", new Integer[]{beanForm.getLicense4(), beanForm.getLicense4Imp()});
		listResultLicense5 = baseService.listRecommendedCandidates("candidate_filter_license", new Integer[]{beanForm.getLicense5(), beanForm.getLicense5Imp()});

		List<List<CandidateForm>> listOfListResult = new ArrayList<List<CandidateForm>>();

		if (beanForm.getEdu1LevelImp() == 10) {
			listOfListResult.add(listResult1Edu);
		}
		if (beanForm.getEdu1MajorImp() == 10) {
			listOfListResult.add(listResult1Major);
		}
		if (beanForm.getEdu2LevelImp() == 10) {
			listOfListResult.add(listResult2Edu);
		}
		if (beanForm.getEdu2MajorImp() == 10) {
			listOfListResult.add(listResult2Major);
		}
		if (beanForm.getEdu3LevelImp() == 10) {
			listOfListResult.add(listResult3Edu);
		}
		if (beanForm.getEdu3MajorImp() == 10) {
			listOfListResult.add(listResult3Major);
		}
		if (beanForm.getLang1Imp() == 10) {
			listOfListResult.add(listResultLang1);
		}
		if (beanForm.getLang2Imp() == 10) {
			listOfListResult.add(listResultLang2);
		}
		if (beanForm.getLang3Imp() == 10) {
			listOfListResult.add(listResultLang3);
		}
		if (beanForm.getLicense1Imp() == 10) {
			listOfListResult.add(listResultLicense1);
		}
		if (beanForm.getLicense2Imp() == 10) {
			listOfListResult.add(listResultLicense2);
		}
		if (beanForm.getLicense3Imp() == 10) {
			listOfListResult.add(listResultLicense3);
		}
		if (beanForm.getLicense4Imp() == 10) {
			listOfListResult.add(listResultLicense4);
		}
		if (beanForm.getLicense5Imp() == 10) {
			listOfListResult.add(listResultLicense5);
		}
		
		if (listOfListResult.size() > 0) {
			validIds = getAndIds(listOfListResult);
		} else {
			mergeOrCondition(validIds, listResult1Edu);
			mergeOrCondition(validIds, listResult1Major);
			mergeOrCondition(validIds, listResult2Edu);
			mergeOrCondition(validIds, listResult2Major);
			mergeOrCondition(validIds, listResult3Edu);
			mergeOrCondition(validIds, listResult3Major);
			mergeOrCondition(validIds, listResultLang1);
			mergeOrCondition(validIds, listResultLang2);
			mergeOrCondition(validIds, listResultLang3);
			mergeOrCondition(validIds, listResultSkill1);
			mergeOrCondition(validIds, listResultSkill2);
			mergeOrCondition(validIds, listResultSkill3);
			mergeOrCondition(validIds, listResultSkill4);
			mergeOrCondition(validIds, listResultSkill5);
			mergeOrCondition(validIds, listResultLicense1);
			mergeOrCondition(validIds, listResultLicense2);
			mergeOrCondition(validIds, listResultLicense3);
			mergeOrCondition(validIds, listResultLicense4);
			mergeOrCondition(validIds, listResultLicense5);
		}
		
		// Create the display list.
		List<CandidateForm> finalResult = new ArrayList<CandidateForm>();
		for (int id: validIds) {
			CandidateForm can = new CandidateForm();
			can.setId(id + "");
			
			// Max Education Score is 100.
			double edu1Score = getScoreOfCandidate(id, listResult1Edu) * 2;
			double major1Score = getScoreOfCandidate(id, listResult1Major) * 2;
			double edu2Score = getScoreOfCandidate(id, listResult2Edu) * 2;
			double major2Score = getScoreOfCandidate(id, listResult2Major);
			double edu3Score = getScoreOfCandidate(id, listResult3Edu) * 2;
			double major3Score = getScoreOfCandidate(id, listResult3Major);
			
			// Max language score is 100.
			double lang1Score = getScoreOfCandidate(id, listResultLang1) / 10;
			double lang2Score = getScoreOfCandidate(id, listResultLang2) / 10;
			double lang3Score = getScoreOfCandidate(id, listResultLang3) / 20;
			
			// Max skill score is 100.
			double skill1Score = getScoreOfCandidate(id, listResultSkill1) / 2.5;
			double skill2Score = getScoreOfCandidate(id, listResultSkill2) / 2.5;
			double skill3Score = getScoreOfCandidate(id, listResultSkill3) / 2.5;
			double skill4Score = getScoreOfCandidate(id, listResultSkill4) / 2.5;
			double skill5Score = getScoreOfCandidate(id, listResultSkill5) / 2.5;
			
			if (skill1Score > 20) {
				skill1Score = 20;
			}
			if (skill2Score > 20) {
				skill2Score = 20;
			}
			if (skill3Score > 20) {
				skill3Score = 20;
			}
			if (skill4Score > 20) {
				skill4Score = 20;
			}
			if (skill5Score > 20) {
				skill5Score = 20;
			}
			
			// Max license score is 100.
			double license1Score = getScoreOfCandidate(id, listResultLicense1) * 2;
			double license2Score = getScoreOfCandidate(id, listResultLicense2) * 2;
			double license3Score = getScoreOfCandidate(id, listResultLicense3) * 2;
			double license4Score = getScoreOfCandidate(id, listResultLicense4) * 2;
			double license5Score = getScoreOfCandidate(id, listResultLicense5) * 2;
			
			HreJobCandidate tmp = baseService.getFirstPojo("id", Compare.EQUAL, id, null, null, 0, HreJobCandidate.class);
			if (tmp != null) {
				can.setName(tmp.getLastName() + " " + tmp.getMiddleName() + " " + tmp.getFirstName());
				can.setContactNumber(tmp.getContactNumber());
				can.setEmail(tmp.getEmail());
			}
			
			can.setEduScore(edu1Score + major1Score + edu2Score + major2Score + edu3Score + major3Score);
			can.setLangScore(lang1Score + lang2Score + lang3Score);
			can.setSkillScore(skill1Score + skill2Score + skill3Score + skill4Score + skill5Score);
			can.setLicenseScore(license1Score + license2Score + license3Score + license4Score + license5Score);
			
			int totalWeight = beanForm.getEduWeight() + beanForm.getLangWeight()
					+ beanForm.getSkillWeight() + beanForm.getLicenseWeight();
			
			if (totalWeight == 0) {
				can.setScore(0);
			} else {
				can.setScore(can.getEduScore() * beanForm.getEduWeight()
						+ can.getLangScore() * beanForm.getLangWeight()
						+ can.getSkillScore() * beanForm.getSkillWeight()
						+ can.getLicenseScore() * beanForm.getLicenseWeight());
				can.setScore(can.getScore() / totalWeight);
			}
			
			can.setVacId(vacId + "");
			finalResult.add(can);
		}

		Collections.sort(finalResult);
		return finalResult;
	}
	
	private double getScoreOfCandidate (int id, List<CandidateForm> candidates) {
		if (id <= 0 || candidates == null || candidates.size() <= 0) {
			return 0;
		}

		for (CandidateForm can: candidates) {
			if (id == CommonUtils.parseInt(can.getId(), 0)) {
				return can.getScore();
			}
		}
		return 0;
	}

	private void mergeOrCondition (List<Integer> validIds, List<CandidateForm> listResult) {
		
		if (validIds == null) {
			validIds = new ArrayList<Integer>();
		}

		if (listResult == null) {
			return;
		}

		for (CandidateForm can: listResult) {
			int curId = CommonUtils.parseInt(can.getId(), 0);
			
			if (curId == 0) {
				continue;
			}
			
			boolean exists = false;
			for (Integer id: validIds) {
				if (curId == id) {
					exists = true;
					break;
				}
			}
			
			if (!exists) {
				validIds.add(curId);
			}
		}
	}

	private List<Integer> getAndIds (List<List<CandidateForm>> listOfListResult) {

		List<Integer> result = new ArrayList<Integer>();
		List<CandidateForm> firstList = listOfListResult.get(0);

		for (CandidateForm can: firstList) {
			int id = CommonUtils.parseInt(can.getId(), 0);
			if (id == 0) {
				continue;
			}

			boolean existAll = true;
			for (int ind = 1; ind < listOfListResult.size(); ind++) {
				List<CandidateForm> tmpList = listOfListResult.get(ind);
				boolean exists = false;
				
				for (CandidateForm can2: tmpList) {
					int curId = CommonUtils.parseInt(can2.getId(), 0);
					if (id == curId) {
						exists = true;
					}
				}
				
				if (!exists) {
					existAll = false;
					break;
				}
			}
			
			if (existAll) {
				result.add(id);
			}
		}

		return result;
	}

	private void getParams (int vacId, VacancyUpdateForm beanForm) {
		
		String dbDescription = null;

		HreJobVacancy vacancy = baseService.getFirstPojo("id", Compare.EQUAL, vacId, null, null, 0, HreJobVacancy.class);
		
		if (vacancy == null) {
			return;
		}

		dbDescription = vacancy.getDescription();
		
		if (dbDescription == null) {
			return;
		}

		VacancyUpdateAction.getNumerialDescription(beanForm, dbDescription);
	}
}
